package pos.data;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import pos.domain.SubType;
import pos.domain.Type;
import pos.domain.TypeImpl;
import util.UIDGenerator;

public class JDBCTypeDAO implements ITypeDAO{

	/**
	 * Attribute which create the connection to the DB
	 */
	private Connection conn;

	/**
	 * Constructor
	 */
	public JDBCTypeDAO() {
		conn = (Connection) ConnectionManager.getInstance().checkOut();
	}

	protected void finalize() {
		ConnectionManager.getInstance().checkIn(conn);
	}
	
	public List<Type> getAllTypes() {

		String sql = "SELECT * FROM type";
		PreparedStatement stmt = null;
		ResultSet result = null;
		List<Type> listTypes = null;

		try {
			stmt = conn.prepareStatement(sql);

			result = stmt.executeQuery();
			listTypes = new ArrayList<Type>();

			while (result.next()) {
				Type type = new TypeImpl();
				type.setId(result.getString("idtype"));
				type.setName(result.getString("name"));
				List<SubType> listSubTypes = new JDBCSubTypeDAO().getListSubTypesByEntityAndType(result.getString("idtype"),
						result.getString("identity"));
				if(listSubTypes!=null){
					type.setListSubTypes(listSubTypes);
				}
				listTypes.add(type);

			}

		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("Error Code: " + e.getErrorCode());
			System.out.println("Cause: " + e.getCause());
			e.printStackTrace();
		} finally {
			try {
				if (result != null) {
					conn.close();
				}
				if (stmt != null) {
					conn.close();
				}
			} catch (SQLException e) {
			}
		}

		return listTypes;
	}

	public Type getTypeById(String idType) {

		Type type = new TypeImpl();
		ResultSet result = null;
		PreparedStatement stmt = null;

		// SQL query for select the Types with that ID.
		String sql = "SELECT * FROM type WHERE (idtype = ?)";

		try {
			// Query execution
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, idType);
			result = stmt.executeQuery();

			// Treatment of query
			while (result.next()){
				type.setId(result.getString("idtype"));
				type.setName(result.getString("name"));				
//				type.setEntity(entity);
				List<SubType> listSubTypes = new JDBCSubTypeDAO().getListSubTypesByEntityAndType(result.getString("idtype"),
						result.getString("identity"));
				if(listSubTypes!=null){
					type.setListSubTypes(listSubTypes);
				}
			}

		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("Error Code: " + e.getErrorCode());
			System.out.println("Cause: " + e.getCause());
			e.printStackTrace();
		} finally {

			try {
				if (stmt != null) {
					conn.close();
				}
				if (result != null) {
					conn.close();
				}

			} catch (Exception e) {

			}
		}

		return type;
	}
	
	public Type getTypeByEntity(String idEntity){

		List<Type> listType = new ArrayList<Type>();
		ResultSet result = null;
		PreparedStatement stmt = null;

		// SQL query for select the Types with that ID.
		String sql = "SELECT * FROM type WHERE (identity = ?)";

		try {
			// Query execution
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, idEntity);
			result = stmt.executeQuery();

			// Treatment of query
			while (result.next()){
				Type type = new TypeImpl();
				type.setId(result.getString("idtype"));
				type.setName(result.getString("name"));
				List<SubType> listSubTypes = new JDBCSubTypeDAO().getListSubTypesByEntityAndType(result.getString("idtype"),
						result.getString("identity"));
				if(listSubTypes!=null){
					type.setListSubTypes(listSubTypes);
				}
				listType.add(type);

			}

		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("Error Code: " + e.getErrorCode());
			System.out.println("Cause: " + e.getCause());
			e.printStackTrace();
		} finally {

			try {
				if (stmt != null) {
					conn.close();
				}
				if (result != null) {
					conn.close();
				}

			} catch (Exception e) {

			}
		}

		return listType.get(0);
	}

	public Type getTypeByName(String name) {

		Type type = new TypeImpl();
		ResultSet result = null;
		PreparedStatement stmt = null;

		// SQL query for select the Types with that ID.
		String sql = "SELECT * FROM type WHERE (name = ?)";

		try {
			// Query execution
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, name);
			result = stmt.executeQuery();

			// Treatment of query
			while (result.next()){
				type.setId(result.getString("idtype"));
				type.setName(result.getString("name"));
				List<SubType> listSubTypes = new JDBCSubTypeDAO().getListSubTypesByEntityAndType(result.getString("idtype"),
						result.getString("identity"));
				if(listSubTypes!=null){
					type.setListSubTypes(listSubTypes);
				}
			}

		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("Error Code: " + e.getErrorCode());
			System.out.println("Cause: " + e.getCause());
			e.printStackTrace();
		} finally {

			try {
				if (stmt != null) {
					conn.close();
				}
				if (result != null) {
					conn.close();
				}

			} catch (Exception e) {

			}
		}

		return type;
	}
		
	public Type insertType(Type type, String idEntity) {

		PreparedStatement stmt = null;
		String idtype = "";
		if(type.getId()==null){
			idtype = UIDGenerator.getInstance().getKey(); // ID for DB
			type.setId(idtype); // ID for object
		}
		idtype = type.getId();
			
			String sql = "INSERT INTO type (idtype,name,identity) VALUES (?,?,?)";

			try {
				stmt = conn.prepareStatement(sql);

				stmt.setString(1, idtype);
				stmt.setString(2, type.getName());
				stmt.setString(3, idEntity); // ojo al crear la entidad debe estar el evento?

				stmt.executeUpdate();
				
			} catch (SQLException e) {
				System.out.println("Message: " + e.getMessage());
				System.out.println("Error Code: " + e.getErrorCode());
				System.out.println("SQL State: " + e.getSQLState());
				System.out.println("Cause: " + e.getCause());
				e.printStackTrace();

			} finally {
				try {
					if (stmt != null) {
						conn.close();
					}
				} catch (SQLException e) {

				}
		}

		return type;
		
	}

	public void removeType(Type type) {

		PreparedStatement stmt = null;
		
		// Also remove the associations with Entity? We want data.

		String sql = "DELETE FROM type WHERE (idtype = ?)";

		try {
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, type.getId());

			stmt.executeUpdate();

		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("Error Code: " + e.getErrorCode());
			System.out.println("Cause: " + e.getCause());
			e.printStackTrace();
		} finally {

			try {
				if (stmt != null) {
					conn.close();
				}
			} catch (Exception e) {

			}
		}
		
	}

	public boolean existType(String name, String idEntity) {
		
		PreparedStatement stmt = null;
		ResultSet result = null;

		String sql = "SELECT * FROM type WHERE (name = ?) AND (identity = ?)";
		boolean res = false;

		try {

			stmt = conn.prepareStatement(sql);
			stmt.setString(1, name);
			stmt.setString(2, idEntity);
			result = stmt.executeQuery();

			if (result.next()==false) {
				res = false;
			} else {
				res = true;
		}

		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("Error Code: " + e.getErrorCode());
			System.out.println("Cause: " + e.getCause());
			e.printStackTrace();
		} finally {
			try {
				if (stmt != null) {
					conn.close();
				}
				if (result != null) {
					conn.close();
				}
			} catch (Exception e) {
			}
		}
		return res;
	}

	public List<SubType> getSubTypesByEntityAndType(String idType, String idEntity) {
		return new JDBCSubTypeDAO().getListSubTypesByEntityAndType(idType, idEntity);
	}

}
